/*
 * Programmer	:	Baljeet Singh
 * Project	 	:  	Travel Experts Phase 3 (Product Management)
 * Language  	:  	Java
 * Date Written	:   March 10, 2011.
 * Student Id	:	562632	
 */

import java.awt.event.KeyEvent;
import java.awt.event.KeyListener;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Enumeration;

import javax.swing.JButton;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.WindowConstants;
import javax.swing.JFrame;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableColumn;


/**
* This code was edited or generated using CloudGarden's Jigloo
* SWT/Swing GUI Builder, which is free for non-commercial
* use. If Jigloo is being used commercially (ie, by a corporation,
* company or business for any purpose whatever) then you
* should purchase a license for each developer using Jigloo.
* Please visit www.cloudgarden.com for details.
* Use of Jigloo implies acceptance of these licensing terms.
* A COMMERCIAL LICENSE HAS NOT BEEN PURCHASED FOR
* THIS MACHINE, SO JIGLOO OR THIS CODE CANNOT BE USED
* LEGALLY FOR ANY CORPORATE OR COMMERCIAL PURPOSE.
*/
public class ProdPanel extends javax.swing.JPanel {
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	
	//variables declared below
	private  JScrollPane jScrollPane1;
	private  JButton btnAdd;
	private  JScrollPane jScrollPane3;
	private  JScrollPane jScrollPane2;
	private  JTable tblProductSupplier;
	private  JLabel jLabel4;
	private  JButton btnRemovesupplier;
	private  JButton btnAddsupplier;
	private  JTable tblSuppliers;
	private  JLabel jLabel3;
	private  JButton btnDelete;
	private  JButton btnClear;
	private  JButton btnUpdate;
	private  JTextField txtProductName;
	private  JLabel lblProductName;
	private  JTextField txtProductId;
	private  JLabel lblProductId;
	private  JTable tblProducts;
	private  JLabel jLabel2;
	private  JLabel jLabel1;
	private  DefaultTableModel tblProductsModel = new DefaultTableModel();
	private  DefaultTableModel tblSuppliersModel = new DefaultTableModel();
	private  DefaultTableModel tblProductSupplierModel = new DefaultTableModel();
	private  ResultSet rs;
	private  Statement stmt;

	/**
	* Auto-generated main method to display this 
	* JPanel inside a new JFrame.
	*/
	public static void main(String[] args)
	{
		JFrame frame = new JFrame();
		frame.getContentPane().add(new ProdPanel());
		frame.setDefaultCloseOperation(WindowConstants.DISPOSE_ON_CLOSE);
		frame.pack();
		frame.setVisible(true);
	}
	
	public ProdPanel() 
	{
		super();
		initGUI();
		//load all the tables
		try 
		{
			loadproducttable();
			loadsuppliertable();
			loadproductsuppliertable();
			txtProductId.setEnabled(false);
			
		} 
		catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	//Code to draw the Product Management GUI
	private void initGUI() 
	{
		try 
		{
			this.setPreferredSize(new java.awt.Dimension(999, 658));
			this.setLayout(null);
			{
				jLabel1 = new JLabel();
				this.add(jLabel1);
				jLabel1.setText("Product Management");
				jLabel1.setBounds(399, 26, 216, 31);
				jLabel1.setFont(new java.awt.Font("Segoe UI",1,20));
			}
			{
				jLabel2 = new JLabel();
				this.add(jLabel2);
				jLabel2.setText("Products");
				jLabel2.setBounds(97, 96, 76, 24);
				jLabel2.setFont(new java.awt.Font("Segoe UI",1,16));
			}
			{
				jScrollPane1 = new JScrollPane();
				this.add(jScrollPane1);
				jScrollPane1.setBounds(56, 134, 412, 148);
				{
					
					tblProducts = new JTable();
					jScrollPane1.setViewportView(tblProducts);
					tblProducts.setModel(tblProductsModel);
					tblProducts.setBounds(51, 135, 258, 140);
					tblProductsModel.setColumnCount(2);
					tblProducts.addKeyListener(new KeyListener() {
						
						@Override
						public void keyTyped(KeyEvent e)
						{
							// TODO Auto-generated method stub
						}
						
						@Override
						public void keyReleased(KeyEvent e) 
						{
							// TODO Auto-generated method stub
						}
						//code to handle the up and down arrow keys event 
						@Override
						public void keyPressed(KeyEvent e)
						{
							// TODO Auto-generated method stub
							//populating the fields on the form when the down or up arrow key is pressed
								if(e.getKeyCode()==KeyEvent.VK_DOWN)
								{
									if(tblProducts.getRowCount()>tblProducts.getSelectedRow()+1)
									{
										populateForm((tblProducts.getSelectedRow())+1);
										
									}
								}
								else if(e.getKeyCode()==KeyEvent.VK_UP) 
								{
									if(tblProducts.getSelectedRow()==0)
									{
										return;
									}
									if(tblProducts.getSelectedRow() <= tblProducts.getRowCount()-1)
									{
										populateForm((tblProducts.getSelectedRow())-1);
										
									}
									
								}
								try 
								{
									//refresh the supplier and productsupplier tables as per product selected
									loadsuppliertable();
									loadproductsuppliertable();
								} 
								catch (SQLException e1) 
								{
									// TODO Auto-generated catch block
									e1.printStackTrace();
								}
						}

						
					});
					tblProducts.addMouseListener(new MouseAdapter() {
						//code to handle mouse click event on product table
						@Override
						public void mouseClicked(MouseEvent e) 
						{
							// TODO Auto-generated method stub
							super.mouseClicked(e);
							populateForm(tblProducts.getSelectedRow());
							try 
							{
								//refresh the supplier and productsupplier tables as per product selected
								loadsuppliertable();
								loadproductsuppliertable();
							} 
							catch (SQLException e1) 
							{
								// TODO Auto-generated catch block
								e1.printStackTrace();
							}
						}
					});
				}
			}
			{
				lblProductId = new JLabel();
				this.add(lblProductId);
				lblProductId.setText("Product Id");
				lblProductId.setBounds(528, 142, 95, 25);
			}
			{
				txtProductId = new JTextField();
				this.add(txtProductId);
				txtProductId.setBounds(686, 143, 233, 23);
			}
			{
				lblProductName = new JLabel();
				this.add(lblProductName);
				lblProductName.setText("Product Name");
				lblProductName.setBounds(528, 193, 95, 21);
			}
			{
				txtProductName = new JTextField();
				this.add(txtProductName);
				txtProductName.setBounds(686, 192, 233, 23);
			}
			{
				btnAdd = new JButton();
				this.add(btnAdd);
				btnAdd.setText("Add");
				btnAdd.setBounds(499, 254, 70, 28);
				btnAdd.addMouseListener(new MouseAdapter() {
					//code for handling the mouse click on Add button
					@Override
					public void mouseClicked(MouseEvent e) 
					{
						// TODO Auto-generated method stub
						super.mouseClicked(e);
						//check for valid data
						if(!validation())
						{
							//if not valid data then return from the function
							return;
						}
						else
						{
							//if data is valid then insert the product into the products table
							String strSQL = "Insert Into Products (productid, prodname) Values(S_110_1_PRODUCTS.nextval, '"+txtProductName.getText()+"')";
							try 
							{
								stmt.executeUpdate(strSQL);
								clear();
							} 
							catch (SQLException e1) 
							{
								// TODO Auto-generated catch block
								e1.printStackTrace();
							}
							
						}
						
					}
				});
			}
			{
				btnUpdate = new JButton();
				this.add(btnUpdate);
				btnUpdate.setText("Update");
				btnUpdate.setBounds(615, 254, 75, 28);
				btnUpdate.addMouseListener(new MouseAdapter() {
					//code for handling the mouse click on Update button
					@Override
					public void mouseClicked(MouseEvent e)
					{
						// TODO Auto-generated method stub
						super.mouseClicked(e);
						//check to see if table has any row
						if(tblProducts.getRowCount()==0)
						{
							JOptionPane.showMessageDialog(ProdPanel.this, "Nothing in Table to Update", "Error", JOptionPane.ERROR_MESSAGE);
						}
						//check to see if a product is selected to update 
						else if(tblProducts.getSelectedRow()==-1)
						{
							JOptionPane.showMessageDialog(ProdPanel.this, "Please select a product to Edit","Error", JOptionPane.ERROR_MESSAGE);
						}
						//validate the data before updating the database
						else if(!validation())
						{
							//if not valid return from the function
							return;
						}
						else
						{
							//if valid then update the product in the database
							String strSQL = "Update Products set prodname= '"+txtProductName.getText()+"' where ProductId= "+txtProductId.getText();
							try 
							{
								stmt.executeUpdate(strSQL);
								clear();
							} 
							catch (SQLException e1) 
							{
								// TODO Auto-generated catch block
								e1.printStackTrace();
							}
							
						}
					}
				});
			}
			{
				btnClear = new JButton();
				this.add(btnClear);
				btnClear.setText("Clear");
				btnClear.setBounds(738, 254, 73, 28);
				btnClear.addMouseListener(new MouseAdapter() {
					//code for handling the mouse click on Clear button
					@Override
					public void mouseClicked(MouseEvent e) 
					{
						// TODO Auto-generated method stub
						super.mouseClicked(e);
						//call the clear function
						clear();
					}
				});
			}
			{
				btnDelete = new JButton();
				this.add(btnDelete);
				btnDelete.setText("Delete");
				btnDelete.setBounds(863, 253, 71, 29);
				btnDelete.addMouseListener(new MouseAdapter() {
					//code for handling the mouse click on delete button
					@Override
					public void mouseClicked(MouseEvent e) 
					{
						// TODO Auto-generated method stub
						super.mouseClicked(e);
						//check to see if table has any row
						if(tblProducts.getRowCount()==0)
						{
							JOptionPane.showMessageDialog(ProdPanel.this, "Nothing in Table to Delete", "Error", JOptionPane.ERROR_MESSAGE);
						}
						//check to see if a product is selected
						else if(tblProducts.getSelectedRow()==-1)
						{
							JOptionPane.showMessageDialog(ProdPanel.this, "Please select a product to delete", "Error", JOptionPane.ERROR_MESSAGE);
						}
						//check to see if the product has been associated with a supplier
						else if(tblProductSupplier.getRowCount()!=0)
						{
							JOptionPane.showMessageDialog(ProdPanel.this, "Please remove the suppliers with this product");
						}
						//delete the product
						else
						{
							//ask for confirmation before deleting the product
							int i = JOptionPane.showConfirmDialog(ProdPanel.this, "Do you really want to delete "+txtProductName.getText()+" from the list of available products?");
							//if yes is selected perform delete
							if(i==0)
							{
								stmt=Database_Functions.databaseConnection();
								try 
								{
									stmt.executeUpdate("delete from Products where ProductId="+txtProductId.getText());
									//clear the form fields
									clear();
								} 
								catch (SQLException e1) 
								{
									// TODO Auto-generated catch block
									e1.printStackTrace();
								}
							}
						}
					}
				});
			}
			{
				jLabel3 = new JLabel();
				this.add(jLabel3);
				jLabel3.setText("Suppliers");
				jLabel3.setBounds(97, 315, 76, 22);
				jLabel3.setFont(new java.awt.Font("Segoe UI",1,16));
			}
			{
				jScrollPane2 = new JScrollPane();
				this.add(jScrollPane2);
				jScrollPane2.setBounds(56, 368, 335, 175);
				{
					
					tblSuppliers = new JTable();
					jScrollPane2.setViewportView(tblSuppliers);
					tblSuppliers.setModel(tblSuppliersModel);
					tblSuppliers.setBounds(45, 368, 261, 172);
					tblSuppliersModel.setColumnCount(2);
				}
			}
			{
				btnAddsupplier = new JButton();
				this.add(btnAddsupplier);
				btnAddsupplier.setText(">>Add Supplier");
				btnAddsupplier.setBounds(419, 404, 158, 38);
				btnAddsupplier.addMouseListener(new MouseAdapter() {
					//code for handling the mouse click on AddSupplier button
					@Override
					public void mouseClicked(MouseEvent e)
					{
						// TODO Auto-generated method stub
						super.mouseClicked(e);
						//check if a product is selected
						if(txtProductId.getText().equals(""))
						{
							JOptionPane.showMessageDialog(ProdPanel.this, "Select a product first");
						}
						//check if a supplier is selected to add to the product
						else if(tblSuppliers.getSelectedRow()==-1)
						{
							JOptionPane.showMessageDialog(ProdPanel.this, "Select a supplier to add");
						}
						//add the supplier to the product using the insert statement
						else
						{
							String strSQL="insert into PRODUCTS_SUPPLIERS (ProductSupplierId,ProductId, SupplierId) values(S_109_1_PRODUCTS_SUPPLIERS.nextval,'"+txtProductId.getText()+"','"+tblSuppliers.getValueAt(tblSuppliers.getSelectedRow(), 0)+"')";
							try 
							{
								stmt.executeUpdate(strSQL);
								//refresh the supplier and productsupplier tables
								loadsuppliertable();
								loadproductsuppliertable();
							} 
							catch (SQLException e1) 
							{
								// TODO Auto-generated catch block
								e1.printStackTrace();
							}
						}
					}
				});
			}
			{
				btnRemovesupplier = new JButton();
				this.add(btnRemovesupplier);
				btnRemovesupplier.setText("<<Remove Supplier");
				btnRemovesupplier.setBounds(419, 463, 158, 37);
				btnRemovesupplier.addMouseListener(new MouseAdapter() {
					//code for handling the mouse click on RemoveSupplier button
					@Override
					public void mouseClicked(MouseEvent e)
					{
						// TODO Auto-generated method stub
						super.mouseClicked(e);
						//check to see if a product is selected
						if(txtProductId.getText().equals(""))
						{
							JOptionPane.showMessageDialog(ProdPanel.this, "Select a product first");
						}
						//check to see if a supplier is selected to remove from the product
						else if(tblProductSupplier.getSelectedRow()==-1)
					    {
					    	JOptionPane.showMessageDialog(ProdPanel.this, "Select a supplier to remove");	
					    }
						//remove the supplier from the product
					    else
					    {
					    	//confirm befor removing
					    	int i = JOptionPane.showConfirmDialog(ProdPanel.this, "Supplier will be removed from product. Are you sure?");
							if(i==0)
							{
								stmt=Database_Functions.databaseConnection();
								try 
								{
									stmt.executeUpdate("delete from PRODUCTS_SUPPLIERS where productsupplierid = "+tblProductSupplier.getValueAt(tblProductSupplier.getSelectedRow(), 0)) ;
									//refresh the supplier and productsupplier tables
									loadproductsuppliertable();
									loadsuppliertable();
								} 
								catch (SQLException e1) 
								{
									// TODO Auto-generated catch block
									e1.printStackTrace();
								}
							}
					    }
					}
				});
			}
			{
				jLabel4 = new JLabel();
				this.add(jLabel4);
				jLabel4.setText("Product Suppliers");
				jLabel4.setBounds(615, 317, 146, 19);
				jLabel4.setFont(new java.awt.Font("Segoe UI",1,16));
			}
			{
				jScrollPane3 = new JScrollPane();
				this.add(jScrollPane3);
				jScrollPane3.setBounds(595, 368, 370, 175);
				{
					
					tblProductSupplier = new JTable();
					jScrollPane3.setViewportView(tblProductSupplier);
					tblProductSupplier.setModel(tblProductSupplierModel);
					tblProductSupplier.setBounds(487, 368, 255, 172);
					tblProductSupplierModel.setColumnCount(2);
					tblProductSupplier.addMouseListener(new MouseAdapter() {
						//method to handle the mouse click on the productsupplier table
						@Override
						public void mouseClicked(MouseEvent e)
						{
							// TODO Auto-generated method stub
							super.mouseClicked(e);
							int prodsupp=(Integer) tblProductSupplier.getValueAt(tblProductSupplier.getSelectedRow(), 0);
							try 
							{
								//call the function valid_prod_supp passing the selected productsupplierID as a argument
								String errMessage = valid_Prod_Supp(prodsupp);
								if (errMessage != "")
				                {
									//if the product supplier is assosiated with a package or booking then show appropriate message
				                    JOptionPane.showMessageDialog(ProdPanel.this, errMessage);
				                    //referesh the productsupplier table
				                    loadproductsuppliertable();
				                    //return from the function
				                    return;
				                }

							}
							catch (SQLException e1) 
							{
								// TODO Auto-generated catch block
								e1.printStackTrace();
							}
						}
					});
				}
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
	}
	
	//function that refreshes the suppplier table
	public void loadsuppliertable() throws SQLException
	{
		//clear the table first
		while (tblSuppliers.getRowCount() > 0)
		 {
			  ((DefaultTableModel)tblSuppliers.getModel()).removeRow(0);
		 }
		 //connect to database
		stmt=Database_Functions.databaseConnection();
		//execute the select statement to get the packages from database
		
		rs= Database_Functions.selectQuery("suppliers", "supplierid","productid",txtProductId.getText());
		ResultSetMetaData rsmd = rs.getMetaData();
		String [] header=new String[rsmd.getColumnCount()];
		for(int j=1;j<=rsmd.getColumnCount();j++)
		{
			header[j-1]=rsmd.getColumnLabel(j);
		}
		while(rs.next())
		{
			
			Object [] myP = {rs.getInt(1),rs.getString(2)};
			//add the object to the table
			tblSuppliersModel.addRow(myP);
		}
		
		//code to display the header names in the table
		Enumeration<TableColumn> cols = tblSuppliers.getColumnModel().getColumns();
		int i=0;
		while (cols.hasMoreElements())
		{
			TableColumn col = cols.nextElement();
			col.setHeaderValue(header[i++]);
		}
		
	}
	
	//function to refresh the productsupplier table
	public void loadproductsuppliertable() throws SQLException
	{
		//clear the table first
		while (tblProductSupplier.getRowCount() > 0)
		 {
			  ((DefaultTableModel)tblProductSupplier.getModel()).removeRow(0);
		 }
		 //connect to database
		stmt=Database_Functions.databaseConnection();
		//execute the select statement to get the packages from database
		if(txtProductId.getText().equals(""))
		{
			//if no product is selected display only the column names of the table
			Enumeration<TableColumn> cols = tblProductSupplier.getColumnModel().getColumns();
			TableColumn col = cols.nextElement();
			col.setHeaderValue("PRODUCTSUPPLIERID");
			TableColumn col1 = cols.nextElement();
			col1.setHeaderValue("SUPPLIERNAME");
			return;
		}
		//if a product is selected populate the table with the associated suppliers using the select query
		String sql="select productsupplierid, a.productid, a.supplierid, b.supname  from products_suppliers a  left outer join suppliers b  on a.supplierid = b.supplierid  where productid = "+txtProductId.getText()+" order by a.supplierid ";
		rs = Database_Functions.selectprodsupp(sql);
		ResultSetMetaData rsmd = rs.getMetaData();
		String [] header=new String[rsmd.getColumnCount()];
		for(int j=1;j<=rsmd.getColumnCount();j++)
		{
			header[j-1]=rsmd.getColumnLabel(j);
		}
		while(rs.next())
		{
			
			Object [] myP = {rs.getInt(1),rs.getString(4)};
			//add the object to the table
			tblProductSupplierModel.addRow(myP);
		}
		
		//code to display the header names in the table
		Enumeration<TableColumn> cols = tblProductSupplier.getColumnModel().getColumns();
		int i=0;
		while (cols.hasMoreElements())
		{
			TableColumn col = cols.nextElement();
			col.setHeaderValue(header[i]);
			i=i+3;
		}
		
	}
	
	//function to refresh the product table
	public void loadproducttable() throws SQLException
	{
		//clear the table first
		while (tblProducts.getRowCount() > 0)
		 {
			  ((DefaultTableModel)tblProducts.getModel()).removeRow(0);
		 }
		 //connect to database
		stmt=Database_Functions.databaseConnection();
		//execute the select statement to get the packages from database
		
		rs= Database_Functions.selectQuery("products", "productid","","");
		ResultSetMetaData rsmd = rs.getMetaData();
		String [] header=new String[rsmd.getColumnCount()];
		for(int j=1;j<=rsmd.getColumnCount();j++)
		{
			header[j-1]=rsmd.getColumnLabel(j);
		}
		while(rs.next())
		{
			
			Object [] myP = {rs.getInt(1),rs.getString(2)};
			//add the object to the table
			tblProductsModel.addRow(myP);
		}
		
		//code to display the header names in the table
		Enumeration<TableColumn> cols = tblProducts.getColumnModel().getColumns();
		int i=0;
		while (cols.hasMoreElements())
		{
			TableColumn col = cols.nextElement();
			col.setHeaderValue(header[i++]);
		}
	}
	
	//fucntion to check if a product-supplier is associated with a package or booking
	public String valid_Prod_Supp(int prodsupp) throws SQLException
	{
		String errorMessage = "";
		String sql =
            " select 'BD' as ID, a.bookingid, bookingno from bookingdetails a" +
            " inner join bookings b on a.bookingid=b.bookingid" +
            " where productsupplierid = " +prodsupp+
            " union " +
            " select 'PK' as ID, c.packageid, pkgname from packages_products_suppliers c " +
            " inner join packages d on c.packageid=d.packageid " +
            " where productsupplierid = "+prodsupp+" order by ID";
			rs=stmt.executeQuery(sql);
			//building the message with the bookings numbers or the package names if it has one
			if(rs.next())
			{
				errorMessage = "Cannot remove Supplier " + tblProductSupplier.getValueAt(tblProductSupplier.getSelectedRow(), 1) ;
                errorMessage += " "+
                    ". In use on the following Packages or Bookings...\n\n";
                ResultSetMetaData rsmd = rs.getMetaData();
                for(int i=1;i<=rsmd.getColumnCount();i++)
                {
                	if(rs.getString(1).equals("PK"))
                	{
                		errorMessage += "Package ";
                	}
                	else if(rs.getString(1).equals("BK"))
                	{
                		 errorMessage += "Booking No ";
                	}
                	errorMessage +=  " " + rs.getString(3) + "\n";
                }
                	
			}
			return errorMessage;
	}
	
	//function that populates all the form elements once a product is selected
	public void populateForm(int row) 
	{
		
		// TODO Auto-generated method stub
		
		String [] fields=new String[tblProducts.getColumnCount()];
		for(int i=0;i<tblProducts.getColumnCount();i++)
		{
			fields[i]=""+tblProductsModel.getValueAt(row, i);
		}
			txtProductId.setText(fields[0]);
			txtProductName.setText(fields[1]);
				
			
	}	
	
	//validation function
	public boolean validation()
	{
		//check to see if the product name is added or not
		if(txtProductName.getText().equals(""))
		{
			JOptionPane.showMessageDialog(ProdPanel.this, "Enter the name of the product");
			return false;
		}
		return true;
	}
	
	//function that clears the form fields
	public void clear()
	{
		txtProductId.setText("");
		txtProductName.setText("");
		try 
		{
			//refresh the tables
			loadproducttable();
			loadproductsuppliertable();
			loadsuppliertable();
		}
		catch (SQLException e1)
		{
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
			
	}
	
	
}
